<?php

namespace app\api\controller;
// use think\Db;
use app\api\model\PhpexcelModel;
// use app\api\validate\Apivalidate;
class Phpexcel{
    /**
     * 显示上传表单html页面
     */
    public function index() {
        return view();
    }
    /**
     * 上传Excel表格
     * @author 王涛
     * @DataTime 2018-08-08T09:55:56+0800
     * @return   [type]                   [description]
     */
    public function excelUploading()
    {
        if(request()->isPost()){
            if ($_FILES["file"]["error"] > 0){
                $error['errorCode'] = $_FILES["file"]["error"];
                return apiResponse(201,"上传文件失败", $error);
            }else{
                if($_FILES["file"]["size"] > 5242880 ) return apiResponse(201,"上传的文件不能大于5M", '');
                //检查目录是否存在  循环创建目录
                if (!is_dir("upload/phpexcel/". date('Y-m-d') . '/')) {
                    mkdir("upload/phpexcel/". date('Y-m-d') . '/', 0777, true);
                }
                $temp = explode('.', $_FILES["file"]["name"]);
                $ext = uniqid() . '.' . end($temp);
                $excelPath['excelPath'] = '/' . "upload/phpexcel/". date('Y-m-d') . '/' . $ext;
                move_uploaded_file($_FILES["file"]["tmp_name"],"upload/phpexcel/". date('Y-m-d') . '/' . $ext);
                return apiResponse(200,"上传文件成功", $excelPath);
            }
        }else{
            return apiResponse(201,"必须post请求", '');
        }
    }
    /**
     * 导入Excel到mysql
     * @author 王涛
     * @DataTime 2018-08-06T20:49:54+0800
     * @return   [type]                   [description]
     */
    public function excelToMysql()
    {
        if(request()->isPost()){
            header("Content-Type:text/html;charset=utf-8");
            // vendor('phpexcel.Classes.PHPExcel');
            // $PHPExcel = new \PHPExcel();
            // vendor('phpexcel.Classes.PHPExcel.Reader.Excel2007');
            // $PHPWriter = new \PHPExcel_Writer_Excel2007();
            vendor('phpexcel.Classes.PHPExcel.IOFactory');
            $IOF = new \PHPExcel_IOFactory();

            // $filePath = './upload/phpexcel/2018-08-08/5b41cb4070057.xls';//excel文件的地址
            if(empty(input('excelPath')))  return apiResponse(201,"路径不能为空","");
            $filePath = input('excelPath');//excel文件的地址
            if($filePath){
                //如果上传文件成功，就执行导入excel操作
                $objReader = $IOF::createReader('Excel2007');
                $objPHPExcel = $IOF::load($filePath);
                $sheet = $objPHPExcel->getSheet(0);
                $highestRow = $sheet->getHighestRow(); // 取得总行数
                $highestColumn = $sheet->getHighestColumn(); // 取得总列数
                // var_dump($highestRow);die;
                //循环读取excel文件,读取一条,插入一条
                for( $j = 4; $j <= $highestRow; $j++){
                    for($k='A'; $k<=$highestColumn; $k++){
                        $str .= $objPHPExcel->getActiveSheet()->getCell("$k$j")->getValue().'\\'; //读取单元格
                        // $str .= iconv("UTF-8","GBK",$objPHPExcel->getActiveSheet()->getCell("$k$j")->getValue()).'\\'; //读取单元格
                        if (empty($str)) return apiResponse(201,"不能有空行存在", '');
                    }
                    if (empty($str)) return apiResponse(201,"不能有空行存在", '');
                    $strs[] = explode("\\",$str);
                    unset($str);
                    // mysql_query("set names 'gb2312'");//这就是指定数据库字符集，一般放在连接数据库后面就系了
                }
                $phpex = new PhpexcelModel;
                $obd = $phpex->obdExcelToMysql($strs);
                if ($obd != 0) {
                    // unlink($filePath); //删除上传的excel文件
                    return apiResponse(200,"导入成功", '');
                }else{
                    return apiResponse(201,"导入失败", '');
                }
            }else{
                return apiResponse(201,"上传的文件不存在", '');
            }
        }else{
            return apiResponse(201,"必须post请求", '');
        }
    }
    public function mysqlToExcel()
    {
        if(request()->isPost()){
            vendor('phpexcel.Classes.PHPExcel');
            $PHPExcel = new \PHPExcel();
            vendor('phpexcel.Classes.PHPExcel.Writer.Excel2007');
            $PHPWriter = new \PHPExcel_Writer_Excel2007();

            vendor('phpexcel.Classes.PHPExcel.Reader.Excel2007');
            $PHPReader = new \PHPExcel_Reader_Excel2007();
            vendor('PHPExcel.Reader.Excel5');
            $PHPReaderExcel5 = new \PHPExcel_Reader_Excel5();
            vendor('PHPExcel.Reader.Excel2003XML');
            $PHPReaderExcel2003XML = new \PHPExcel_Reader_Excel2003XML();
            // vendor('phpexcel.Classes.PHPExcel.IOFactory');
            // $IOF = new \PHPExcel_IOFactory();
            // var_dump($IOF);die;
            $filePath = '/upload/phpexcel/2018-08-08/5b41856782906.xls';//excel文件的地址
            //读取Excel文件
            $a = $IReader->load($filePath);
            var_dump($a);die;
        }else{
            return apiResponse(201,"必须post请求", '');
        }
    }
}